package CreateNewItem;


import CreateNewItem.WIN.DnkFunctions;

import java.sql.*;

public class SQLEXEC {
//    public int getSite() {
//        return site;
//    }

    public void setSite(int x) {
        if (x==0){

            site="dbLI";


        } else if (x==1){
            site = "dbBL";
        }
    }

    String site;

    public void setWareHouse(int x) {
        if (x==0){

            wareHouse="2";  //2 for LI ware house


        } else if (x==1){
            wareHouse = "3";   //3 for BK warehouse
        }
//        this.wareHouse = wareHouse;
    }

    String wareHouse;


    //method for 1 set of field to be updated
    public void StmExecute(String Ta, String Tb,String Wa, String Wb, String Wo, String Oa, String Ob, String a1, String b1){
        String statement=null;



        statement = "UPDATE "+Ta+" AS a INNER JOIN "+Tb+" " +
                "AS b ON a."+ Oa +" = b."+ Ob +" " +
                "SET " +

                "a."+a1+" = b."+b1+"" +



                " "+Wa +" "+Wo+" "+Wb +" "+                                   ";";
        sqlprocess(statement);
    }
    //method for 2 set of field to be updated
    public void StmExecute(String Ta, String Tb, String Wa, String Wb, String Wo, String Oa, String Ob, String a1, String b1, String a2, String b2){
        String statement=null;



        statement = "UPDATE "+Ta+" AS a INNER JOIN "+Tb+" " +
                "AS b ON a."+ Oa +" = b."+ Ob +" " +
                "SET " +

                "a."+a1+" = b."+b1+" ," +
                "a."+a2+" = b."+b2+"" +

                " "+Wa +" "+Wo+" "+Wb +" "+                                   ";";
        sqlprocess(statement);
    }
    //method for 3 set of field to be updated
    public void StmExecute(String Ta, String Tb, String Wa, String Wb, String Wo, String Oa, String Ob, String a1, String b1, String a2, String b2, String a3, String b3){
        String statement=null;



        statement = "UPDATE "+Ta+" AS a INNER JOIN "+Tb+" " +
                "AS b ON a."+ Oa +" = b."+ Ob +" " +
                "SET " +

                "a."+a1+" = b."+b1+" ," +
                "a."+a2+" = b."+b2+" ," +
                "a."+a3+" = b."+b3+"" +

                " "+Wa +" "+Wo+" "+Wb +" "+                                   ";";
        sqlprocess(statement);
    }
    //method for 4 set of field to be updated
    public void StmExecute(String Ta, String Tb, String Wa, String Wb, String Wo, String Oa, String Ob,String a1, String b1,String a2, String b2,String a3, String b3,String a4, String b4){
        String statement=null;



        statement = "UPDATE "+Ta+" AS a INNER JOIN "+Tb+" " +
                "AS b ON a."+ Oa +" = b."+ Ob +" " +
                "SET " +

                "a."+a1+" = b."+b1+" ," +
                "a."+a2+" = b."+b2+" ," +
                "a."+a3+" = b."+b3+" ," +
                "a."+a4+" = b."+b4+"" +

                " "+Wa +" "+Wo+" "+Wb +" "+                                   ";";
        sqlprocess(statement);
    }
    //method for 5 set of field to be updated
    public void StmExecute(String Ta, String Tb, String Wa, String Wb, String Wo, String Oa, String Ob,String a1, String b1,String a2, String b2,String a3, String b3,String a4, String b4,String a5, String b5
    ){
        String statement=null;



        statement = "UPDATE "+Ta+" AS a INNER JOIN "+Tb+" " +
                "AS b ON a."+ Oa +" = b."+ Ob +" " +
                "SET " +

                "a."+a1+" = b."+b1+" ," +
                "a."+a2+" = b."+b2+" ," +
                "a."+a3+" = b."+b3+" ," +
                "a."+a4+" = b."+b4+" ," +
                "a."+a5+" = b."+b5+"" +

                " "+Wa +" "+Wo+" "+Wb +" "+                                   ";";
        sqlprocess(statement);
    }
    //method for 6 set of field to be updated
    public void StmExecute(String Ta, String Tb, String Wa, String Wb, String Wo, String Oa, String Ob,String a1, String b1,String a2, String b2,String a3, String b3,String a4, String b4,String a5, String b5,String a6, String b6
    ){
        String statement=null;



        statement = "UPDATE "+Ta+" AS a INNER JOIN "+Tb+" " +
                "AS b ON a."+ Oa +" = b."+ Ob +" " +
                "SET " +

                "a."+a1+" = b."+b1+" ," +
                "a."+a2+" = b."+b2+" ," +
                "a."+a3+" = b."+b3+" ," +
                "a."+a4+" = b."+b4+" ," +
                "a."+a5+" = b."+b5+" ," +
                "a."+a6+" = b."+b6+"" +

                " "+Wa +" "+Wo+" "+Wb +" "+                                   ";";
        sqlprocess(statement);
    }
    //method for 7 set of field to be updated
    public void StmExecute(String Ta, String Tb, String Wa, String Wb, String Wo, String Oa, String Ob,String a1, String b1,String a2, String b2,String a3, String b3,String a4, String b4,String a5, String b5,String a6, String b6,String a7, String b7
    ){
        String statement=null;



        statement = "UPDATE "+Ta+" AS a INNER JOIN "+Tb+" " +
                "AS b ON a."+ Oa +" = b."+ Ob +" " +
                "SET " +

                "a."+a1+" = b."+b1+" ," +
                "a."+a2+" = b."+b2+" ," +
                "a."+a3+" = b."+b3+" ," +
                "a."+a4+" = b."+b4+" ," +
                "a."+a5+" = b."+b5+" ," +
                "a."+a6+" = b."+b6+" ," +
                "a."+a7+" = b."+b7+"" +

                " "+Wa +" "+Wo+" "+Wb +" "+                                   ";";
        sqlprocess(statement);
    }
    //method for 8 set of field to be updated
    public void StmExecute(String Ta, String Tb, String Wa, String Wb, String Wo, String Oa, String Ob,String a1, String b1,String a2, String b2,String a3, String b3,String a4, String b4,String a5, String b5,String a6, String b6,String a7, String b7,String a8, String b8
    ){
        String statement=null;



        statement = "UPDATE "+Ta+" AS a INNER JOIN "+Tb+" " +
                "AS b ON a."+ Oa +" = b."+ Ob +" " +
                "SET " +

                "a."+a1+" = b."+b1+" ," +
                "a."+a2+" = b."+b2+" ," +
                "a."+a3+" = b."+b3+" ," +
                "a."+a4+" = b."+b4+" ," +
                "a."+a5+" = b."+b5+" ," +
                "a."+a6+" = b."+b6+" ," +
                "a."+a7+" = b."+b7+" ," +
                "a."+a8+" = b."+b8+"" +

                " "+Wa +" "+Wo+" "+Wb +" "+                                   ";";
        sqlprocess(statement);
    }
    //method for 9 set of field to be updated
    public void StmExecute(String Ta, String Tb, String Wa, String Wb, String Wo, String Oa, String Ob,String a1, String b1,String a2, String b2,String a3, String b3,String a4, String b4,String a5, String b5,String a6, String b6,String a7, String b7,String a8, String b8,String a9, String b9
    ){
        String statement=null;



        statement = "UPDATE "+Ta+" AS a INNER JOIN "+Tb+" " +
                "AS b ON a."+ Oa +" = b."+ Ob +" " +
                "SET " +

                "a."+a1+" = b."+b1+" ," +
                "a."+a2+" = b."+b2+" ," +
                "a."+a3+" = b."+b3+" ," +
                "a."+a4+" = b."+b4+" ," +
                "a."+a5+" = b."+b5+" ," +
                "a."+a6+" = b."+b6+" ," +
                "a."+a7+" = b."+b7+" ," +
                "a."+a8+" = b."+b8+" ," +
                "a."+a9+" = b."+b9+"" +

                " "+Wa +" "+Wo+" "+Wb +" "+                                   ";";
        sqlprocess(statement);
    }
    //method for 10 set of field to be updated
    public void StmExecute(String Ta, String Tb, String Wa, String Wb, String Wo, String Oa, String Ob,String a1, String b1,String a2, String b2,String a3, String b3,String a4, String b4,String a5, String b5,String a6, String b6,String a7, String b7,String a8, String b8,String a9, String b9,String a10, String b10
    ){
        String statement=null;



        statement = "UPDATE "+Ta+" AS a INNER JOIN "+Tb+" " +
                "AS b ON a."+ Oa +" = b."+ Ob +" " +
                "SET " +

                "a."+a1+" = b."+b1+" ," +
                "a."+a2+" = b."+b2+" ," +
                "a."+a3+" = b."+b3+" ," +
                "a."+a4+" = b."+b4+" ," +
                "a."+a5+" = b."+b5+" ," +
                "a."+a6+" = b."+b6+" ," +
                "a."+a7+" = b."+b7+" ," +
                "a."+a8+" = b."+b8+" ," +
                "a."+a9+" = b."+b9+" ," +
                "a."+a10+" = b."+b10+"" +

                " "+Wa +" "+Wo+" "+Wb +" "+                                   ";";
        sqlprocess(statement);
    }


    //Check if Item Exist
    public void CheckDupItem(String tabl, String a){

    }
    //Script to create New Item

    public void DelTblNU(){

        //Clear tblNU table

        sqlprocess("delete * from tblnu;");
    }
    public void CreateNewItemNU(String a, String b, String c, String d, String e, String f,String g,String h,String i,String j,String k,String l,String m,String n,String o,String p,String q,String r){
        String statement=null;

//                                         a        b      c      d    e          f      g     h    i     j      k     l       m           n      o     p   q    r
        sqlprocess("Insert into tblnu (ITEM_NO,PART_NO,BRAND,MODEL,CATALOG,DESCRIPT,R_COST,COST,RETAIL,QOH,CONTROL,LI_WARE,POSITION,NEW_DES,NEW_MOD,YR_L,YR_H,REMARK) values ("+
                "'"+a+"',"    +
                "'"+b+"',"    +
                "'"+c+"',"    +
                "'"+d+"',"    +
                ""+e+","    +
                "'"+f+"',"    +
                ""+g+","    +
                ""+h+","    +
                ""+i+","    +
                ""+j+","    +
                ""+k+","    +
                ""+l+","    +
                "'"+m+"',"    +
                "'"+n+"',"    +
                "'"+o+"',"    +
                ""+p+","    +
                ""+q+","    +
                "'"+r+"')"    +
                ";");
        // ******** WarehouseID #3 is brooklyn.********
//        sqlprocess("INSERT INTO tblmodel ( [part#], make, mdl, modeltype, Description, Beginyr, Endyr, WarehouseID, position, new_des ) " +
//                "SELECT '"+a+"', '"+c+"', '"+d+"', '"+o+"', '"+f+"', "+p+", "+q+", 2, '"+m+"', '"+n+"' " +
//                "FROM tblnu;");
////        original code for above statement
////        sqlprocess("INSERT INTO tblmodel ( [part#], make, mdl, modeltype, Description, Beginyr, Endyr, WarehouseID, position, new_des ) " +
////                "SELECT item_no, brand, model, new_mod, descript, yr_l, yr_h, 3, position, new_des " +
////                "FROM tblnu;");
//
//        sqlprocess("INSERT INTO TBLINVENTORYCOSTING ( INVID, QTY, DATERECEIVED, USED, COST, VENDORID ) " +
//                "SELECT DISTINCT A.INVID, A.INSTOCK, NOW(), 0, 0, NULL " +
//                "FROM TBLINVENTORY AS A INNER JOIN tblnu AS B ON A.[PART#]=B.[ITEM_NO];");



//        " WHERE NOT EXISTS (SELECT * FROM " + Tabl + " where ITEM_NO = " + a + ")" +




        //Execute Statement
//        sqlprocess(statement);
    }

    public void CreateNewItemBC(String a, String b, String c, String d, String e, String f,String g,String h,String i,String j,String k,String l,String m,String n,String o,String p,String q,String r,String s
                                ,String t,String u,String v,String w,String x,String y, String z, String a1){
        String statement=null;


        a="'"+a+"',"  ;    //ITEM_NO
        b="'"+b+"',"  ;    //PART_NO
        c="'"+c+"',"  ;    //SUPP
        d="'"+d+"',"  ;    //DESCRIPT
        e=""+e+""  ;      //CAPA
        f="'"+f+"',"  ;    //PARTLINK
        g="'"+g+"',"  ;    //POSITION
        h="'"+h+"',"  ;    //DNK_NOTE1
        i="'"+i+"',"  ;    //TYG_NO
        j=""+j+","  ;      //TYG_PRC
        k=""+k+","  ;      //TYG_PKG
        l=""+l+","  ;      //TYG_CUFT
        m="'"+m+"',"  ;    //TYG_REMARK
        n="'"+n+"',"  ;    //GD_NO
        o=""+o+","  ;      //GD_PRC
        p=""+p+","  ;      //GD_PKG
        q=""+q+","  ;      //GD_CUFT
        r="'"+r+"',"  ;    //GD_REMARK
        s="'"+s+"',"  ;    //TX_NO
        t=""+t+","  ;      //TX_PRC
        u=""+u+","  ;      //TX_PKG
        v="'"+v+"',"  ;    //TX_REMARK
        w="'"+w+"',"  ;    //PROTEC_NO
        x=""+x+","  ;      //PROTEC_PRC
        y=""+y+","  ;      //PROTEC_PKG
        z=""+z+"," ;         //PROTEC_CUFT
        a1="'"+a1+"',";       //PROTEC_REMARK




        //sample from trn6cat

        statement = "INSERT INTO TBLBCN ( [PART#], SUPPLIER, DESCRIPTION, T24m, T12m, 12MSL, 9msl, 6MSL, 3MSL, INSTOCK, 1mns, 1M, 1_5M, 2M, 3M, 4M, 5M, ONORDER, SHIPPING, TYG_TW, TYG_TX, GD, PROTEC, " +
                "TYG_TW_P, TYG_TX_P, GD_P, PROTEC_P, TYG_TW_K, TYG_TX_K, GD_K, PROTEC_K, [TYG_TW#], [TYG_TX#], [GD#], [PROTEC#], REMARK, DNK_MEMO, " +
                "TYG_TW_MEMO, TYG_TX_MEMO, GD_MEMO, PROTEC_MEMO, RATE, " +
                "TYG_TW_C, TYG_TX_C, GD_C, PROTEC_C, TYG_TW_BO, TYG_TX_BO, GD_BO, PROTEC_BO, OEM_NO, PARTLINK, SUGGEST, TYG_TW_STATUS, TYG_TX_STATUS, GD_STATUS, PROTEC_STATUS, NEW, CAPA, MANUAL ) " +
        "SELECT "+a+" "+c+" "+d+" 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, " +
                ""+j+" "+t+" "+o+" "+x+""+k+" "+u+" "+p+" "+y+" "+i+" "+s+" "+n+" "+w+" NULL, "+h+" " +
                ""+m+" "+v+" "+r+" "+a1+" 0, " +
                ""+l+" "+l+" "+q+" "+z+" 0, 0, 0, 0, "+b+" "+f+" 0, NULL, NULL, NULL, NULL, 0,IIF('"+e+"'='Y',-1,0), 0 ";
//                "WHERE ITEM_NO NOT IN (SELECT "+a+" FROM TBLBCN);" ;


//
//        statement = "Insert into " +
//                "TBLBCN (ITEM_NO,PART_NO,SUPP,DESCRIPT,CAPA,PARTLINK,POSITION,DNK_NOTE1, " +               //8
//                "TYG_NO,TYG_PRC,TYG_PKG,TYG_CUFT,TYG_REMARK, " +             //TYG_TAIWAN                 //5
//                "GD_NO,GD_PRC,GD_PKG,GD_CUFT, GD_REMARK, " +                 //GOLDEN                     //5
//                "TX_NO,TX_PRC,TX_REMARK, " +                         //TYG_TEXAS                  //4
//                "PROTEC_NO, PROTEC_PRC,PROTEC_PKG, PROTEC_CUFT" +         //PROT (AP)                    //4
//                ") values ("+
//                "'"+a+"',"    +                       //ITEM_NO
//                "'"+b+"',"    +                       //PART_NO
//                "'"+c+"',"    +                       //SUPP
//                "'"+d+"',"    +                       //DESCRIPT
//                ""+e+","    +                         //CAPA
//                "'"+f+"',"    +                       //PARTLINK
//                "'"+g+"',"    +                       //POSITION
//                "'"+h+"',"    +                       //DNK_NOTE1
//                "'"+i+"',"    +                       //TYG_NO
//                ""+j+","    +                         //TYG_PRC
//                ""+k+","    +                         //TYG_PKG
//                ""+l+","    +                         //TYG_CUFT
//                "'"+m+"',"    +                       //TYG_REMARK
//                "'"+n+"',"    +                       //GD_NO
//                ""+o+","    +                         //GD_PRC
//                ""+p+","    +                         //GD_PKG
//                ""+q+","    +                         //GD_CUFT
//                "'"+r+"',"    +                       //GD_REMARK
//                "'"+s+"',"    +                       //TX_NO
//                ""+t+","    +                         //TX_PRC
////                ""+u+","    +                         //TX_PKG
//                "'"+u+"',"    +                       //TX_REMARK
//                "'"+v+"',"    +                       //PROTEC_NO
//                ""+w+","    +                         //PROTEC_PRC
//                ""+x+","    +                         //PROTEC_PKG
//                ""+y+")";                             //PROTEC_CUFT
//                ""+a1+")"    +                        //PROTEC_CUFT
//                "'"+a1+"')"    +                       //GD_REMARK
//                "'"+a2+"')"    +                       //GD_REMARK




        //Execute Statement
        sqlprocess(statement);
    }
    public void CreateNewItemHF(String a, String b, String c, String d, String e, String f,String g,String h,String i,String j,String k,String l,String m,String n,String o,String p,String q,String r,String s
            ,String t,String u,String v,String w,String x,String y, String z, String a1){
        String statement=null;


        a="'"+a+"'"  ;    //ITEM_NO
        b="'"+b+"'"  ;    //PART_NO
        c="'"+c+"'"  ;    //SUPP
        d="'"+d+"'"  ;    //DESCRIPT
        e=""+e+""  ;      //CAPA
        f="'"+f+"'"  ;    //PARTLINK
        g="'"+g+"'"  ;    //POSITION
        h="'"+h+"'"  ;    //DNK_NOTE1
        i="'"+i+"'"  ;    //TYG_NO
        j=""+j+""  ;      //TYG_PRC
        k=""+k+""  ;      //TYG_PKG
        l=""+l+""  ;      //TYG_CUFT
        m="'"+m+"'"  ;    //TYG_REMARK
        n="'"+n+"'"  ;    //GD_NO
        o=""+o+""  ;      //GD_PRC
        p=""+p+""  ;      //GD_PKG
        q=""+q+""  ;      //GD_CUFT
        r="'"+r+"'"  ;    //GD_REMARK
        s="'"+s+"'"  ;    //TX_NO
        t=""+t+""  ;      //TX_PRC
        u=""+u+""  ;      //TX_PKG
        v="'"+v+"'"  ;    //TX_REMARK
        w="'"+w+"'"  ;    //PROTEC_NO
        x=""+x+""  ;      //PROTEC_PRC
        y=""+y+""  ;      //PROTEC_PKG
        z=""+z+"" ;         //PROTEC_CUFT
        a1="'"+a1+"'";       //PROTEC_REMARK

        statement = "INSERT INTO TBLHFN ( " +
                "[PART#], SUPPLIER, DESCRIPTION, T24m, T12m, 12MSL, 9msl, 6MSL, 3MSL, INSTOCK, 1mns, 1M, 1_5M, 2M, 3M, 4M, 5M, ONORDER, SHIPPING, TYG_TW, TYG_TX, GD, " +
                "TYG_TW_P, TYG_TX_P, GD_P, TYG_TW_K, TYG_TX_K, GD_K, [TYG_TW#], [TYG_TX#], [GD#], REMARK, DNK_MEMO, TYG_TW_MEMO, TYG_TX_MEMO, GD_MEMO, RATE, " +
                "TYG_TW_C, TYG_TX_C, GD_C, TYG_TW_BO, TYG_TX_BO, GD_BO, OEM_NO, PARTLINK, SUGGEST, TYG_TW_STATUS, TYG_TX_STATUS, GD_STATUS, NEW, CAPA, MANUAL ) " +

                "SELECT "+a+", "+b+", "+d+", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, " +
                "IIF(ISNULL("+j+"),0,"+j+"), IIF(ISNULL("+t+"),0,"+t+"), IIF(ISNULL("+o+"),0,"+o+"), IIF(ISNULL("+k+"),0,"+k+"), IIF(ISNULL("+k+"),0,"+k+"), IIF(ISNULL("+p+"),0,"+p+"), "+i+", "+s+", "+n+", " +
                "NULL, "+h+", "+m+", NULL, "+r+", 0, IIF(ISNULL("+l+"),0,"+l+"), IIF(ISNULL("+l+"),0,"+l+"), IIF(ISNULL("+q+"),0,"+q+"), 0, 0, 0, "+b+", "+f+", 0, NULL, NULL, NULL, 0, IIF('"+e+"'='Y',-1,0), 0 " ;



        //Execute Statement
        sqlprocess(statement);
    }
    public void CreateNewItemLT(String a, String b, String c, String d, String e, String f,String g,String h,String i,String j,String k,String l,String m,String n,String o,String p){
        String statement=null;


        a="'"+a+"'"  ;    //ITEM_NO
        b="'"+b+"'"  ;    //PART_NO
        c="'"+c+"'"  ;    //SUPP
        d="'"+d+"'"  ;    //DESCRIPT
        e=""+e+""  ;      //CAPA
        f="'"+f+"'"  ;    //PARTLINK
        g="'"+g+"'"  ;    //POSITION
        h="'"+h+"'"  ;    //DNK_NOTE1  //REMARK <- Up to this point doesn't change
        i="'"+i+"'"  ;    //GN_NO
        j=""+j+""  ;      //GN_PRC
        k=""+k+""  ;      //GN_PKG
        l="'"+l+"'"  ;      //GN_REMARK
        m="'"+m+"'"  ;    //DEPO_NO
        n=""+n+""  ;    //DEPO_PRC
        o=""+o+""  ;      //DEPO_PKG
        p="'"+p+"'"  ;      //DEPO_REMARK
//        q=""+q+","  ;      //KV_NO
//        r="'"+r+"',"  ;    //KV_PRC
//        s="'"+s+"',"  ;    //KV_PKG
//        t=""+t+","  ;      //KV_REMARK

        //sample from trn6cat
        statement ="INSERT INTO TBLLTN ( " +
                "[PART#], SUPPLIER, DESCRIPTION,OEM_NO,PARTLINK, SUGGEST, CAPA, t24m, t12m, 12MSL, 9msl, 6MSL, 3MSL, RATE, INSTOCK, 1mns, 1M, 1_5M, 2M, 3M, 4M, 5M, ONORDER, SHIPPING, " +
                "GD, GENERA, DEPO, ee, REMARK, " +
                "DNK_MEMO, GENERA_MEMO, DEPO_MEMO, NEW, GENERA_P, DEPO_P, GENERA_K, DEPO_K, [GENERA#], [DEPO#], " +
                "GENERA_C, DEPO_C,GENERA_BO, DEPO_BO ) " +

                "SELECT " +
                ""+a+", "+c+", "+d+", "+b+", "+f+", 0, IIF('"+e+"'='Y',-1,0), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, " +
                "0, 0, 0, 0, 'NULL', " +
                ""+h+", "+l+","+p+", 0, "+j+", "+n+", "+k+", "+o+", "+i+", "+m+", " +
                "0, 0, 0, 0; ";
        //Execute Statement
        sqlprocess(statement);
    }
    public void CreateNewItemCL(String a, String b, String c, String d, String e, String f,String g,String h,String i,String j,String k,String l,String m,String n,String o,String p,String q,String r,String s
            ,String t,String u,String v,String w,String x,String y, String z, String a1, String a2, String a3, String a4, String a5, String a6, String a7, String a8, String a9, String a10, String a11, String a12, String a13, String a14, String a15, String a16){
        String statement=null;


        a="'"+a+"'"  ;    //ITEM_NO
        b="'"+b+"'"  ;    //PART_NO
        c="'"+c+"'"  ;    //SUPP
        d="'"+d+"'"  ;    //DESCRIPT
        e=""+e+""  ;      //CAPA
        f="'"+f+"'"  ;    //PARTLINK
        g="'"+g+"'"  ;    //POSITION
        h="'"+h+"'"  ;    //DNK_NOTE1   REMARK
        i="'"+i+"'"  ;    //TYG_NO
        j=""+j+""  ;      //TYG_PRC
        k=""+k+""  ;      //TYG_PKG
        l=""+l+""  ;      //TYG_CUFT
        m="'"+m+"'"  ;    //TYG_REMARK
        n="'"+n+"'"  ;    //GD_NO
        o=""+o+""  ;      //GD_PRC
        p=""+p+""  ;      //GD_PKG
        q=""+q+""  ;      //GD_CUFT
        r="'"+r+"'"  ;    //GD_REMARK
        s="'"+s+"'"  ;    //TX_NO
        t=""+t+""  ;      //TX_PRC
        u=""+u+""  ;      //TX_PKG
        v="'"+v+"'"  ;    //TX_REMARK
        w="'"+w+"'"  ;    //PROTEC_NO
        x=""+x+""  ;      //PROTEC_PRC
        y=""+y+""  ;      //PROTEC_PKG
        z=""+z+"" ;       //PROTEC_CUFT
        a1="'"+a1+"'";    //PROTEC_REMARK
        a2="'"+a2+"'"  ;   //GN_NO
        a3=""+a3+""  ;     //GN_PRC
        a4=""+a4+""  ;     //GN_PKG
        a5=""+a5+""  ;     //GN_CUFT
        a6="'"+a6+"'"  ;   //GN_REMARK
        a7="'"+a7+"'"  ;   //DEPO_NO
        a8=""+a8+""  ;     //DEPO_PRC
        a9=""+a9+""  ;     //DEPO_PKG
        a10=""+a10+""  ;    //DEPO_CUFT
        a11="'"+a11+"'"  ;  //DEPO_REMARK
        a12="'"+a12+"'"  ;  //KV_NO
        a13=""+a13+""  ;    //KV_PRC
        a14=""+a14+""  ;    //KV_PKG
        a15=""+a15+""  ;    //KV_CUFT
        a16="'"+a16+"'"  ;  //KV_REMARK
        //sample from trn6cat

//        statement ="INSERT INTO TBLCLN ( " +
//                "[PART#], SUPPLIER, DESCRIPTION,OEM_NO,PARTLINK, SUGGEST, CAPA, t24m, t12m, 12MSL, 9msl, 6MSL, 3MSL, RATE, INSTOCK, 1mns, 1M, 1_5M, 2M, 3M, 4M, 5M, ONORDER, SHIPPING, " +
//                "GD, GENERA, DEPO, ee, REMARK, " +
//                "DNK_MEMO, GENERA_MEMO, DEPO_MEMO, NEW, GENERA_P, DEPO_P, GENERA_K, DEPO_K, [GENERA#], [DEPO#], " +
//                "GENERA_C, DEPO_C,GENERA_BO, DEPO_BO ) " +
//
//                "SELECT " +
//                ""+a+", "+c+", "+d+", "+b+", "+f+", 0, IIF('"+e+"'='Y',-1,0), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, " +
//                "0, 0, 0, 0, '"+a6+"', " +
//                ""+h+", "+l+","+p+", 0, "+j+", "+n+", "+k+", "+o+", "+i+", "+m+", " +
//                "0, 0, 0, 0; ";
        statement=("INSERT INTO TBLCLN ( [PART#], SUPPLIER, DESCRIPTION, T24m, T12m, 12MSL, 9msl, 6MSL, 3MSL, INSTOCK, 1mns, 1M, 1_5M, 2M, 3M, 4M, 5M, ONORDER, SHIPPING, TYG_TW, TYG_TX, GD, GN, " +   //1
                "TYG_TW_P, TYG_TX_P, GD_P, GN_P, " +                                                                                                                                                    //2
                "TYG_TW_K, TYG_TX_K, GD_K, GN_K, " +                                                                                                                                                    //3
                "[TYG_TW#], [TYG_TX#], [GD#], [GN#], REMARK, DNK_MEMO, " +                                                                                                                              //4
                "TYG_TW_MEMO, TYG_TX_MEMO, GD_MEMO, DEPO_MEMO, GN_MEMO, RATE, " +                                                                                                                                  //5
                "TYG_TW_C, TYG_TX_C, GD_C, GN_C, " +                                                                                                                                                    //6
                "TYG_TW_BO, TYG_TX_BO, GD_BO, GN_BO, OEM_NO, PARTLINK, SUGGEST, TYG_TW_STATUS, TYG_TX_STATUS, GD_STATUS, GN_STATUS, NEW, CAPA, MANUAL, DEPO_P,	DEPO_K,	[DEPO#] ) " +                   //7

                "SELECT "+a+", "+c+", "+d+", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, " +                                                                                            //1
                "IIF(ISNULL("+j+"),0,"+j+"), IIF(ISNULL("+t+"),0,"+t+"), IIF(ISNULL("+o+"),0,"+o+"), IIF(ISNULL("+a3+"),0,"+a3+"), " +                                                                  //2
                "IIF(ISNULL("+k+"),0,"+k+"), IIF(ISNULL("+u+"),0,"+u+"), IIF(ISNULL("+p+"),0,"+p+"), IIF(ISNULL("+a4+"),0,"+a4+"), " +                                                                  //3
                ""+i+", "+s+", "+n+", "+a2+", NULL, "+h+", " +                                                                                                                                         //4
                ""+m+", "+v+", "+r+", "+a11+" , "+a6+", 0, " +                                                                                                                                              //5
                "IIF(ISNULL("+l+"),0,"+l+"), IIF(ISNULL("+l+"),0,"+l+"), IIF(ISNULL("+q+"),0,"+q+"), " +                                                                                //6
                "0, 0, 0, 0, 0, "+b+", "+f+", 0, NULL, NULL, NULL, NULL, 0,IIF('"+e+"'='Y',-1,0),0, IIF(ISNULL("+a8+"),0,"+a8+"),	IIF(ISNULL("+a9+"),0,"+a9+"),	IIF(ISNULL("+a7+"),0,"+a7+")");       //7

        //Execute Statement
        sqlprocess(statement);
    }
    public void CreateNewItemMR(String a, String b, String c, String d, String e, String f,String g,String h,String i,String j,String k,String l,String m,String n,String o,String p,String q,String r,String s
            ,String t,String u,String v,String w,String x,String y, String z, String a1, String a2, String a3, String a4, String a5, String a6, String a7, String a8, String a9, String a10, String a11, String a12, String a13, String a14, String a15, String a16){
        String statement=null;


        a="'"+a+"'"  ;    //ITEM_NO
        b="'"+b+"'"  ;    //PART_NO
        c="'"+c+"'"  ;    //SUPP
        d="'"+d+"'"  ;    //DESCRIPT
        e=""+e+""  ;      //CAPA
        f="'"+f+"'"  ;    //PARTLINK
        g="'"+g+"'"  ;    //POSITION
        h="'"+h+"'"  ;    //DNK_NOTE1   REMARK
        i="'"+i+"'"  ;    //TYG_NO
        j=""+j+""  ;      //TYG_PRC
        k=""+k+""  ;      //TYG_PKG
        l=""+l+""  ;      //TYG_CUFT
        m="'"+m+"'"  ;    //TYG_REMARK
        n="'"+n+"'"  ;    //GD_NO
        o=""+o+""  ;      //GD_PRC
        p=""+p+""  ;      //GD_PKG
        q=""+q+""  ;      //GD_CUFT
        r="'"+r+"'"  ;    //GD_REMARK
        s="'"+s+"'"  ;    //TX_NO
        t=""+t+""  ;      //TX_PRC
        u=""+u+""  ;      //TX_PKG
        v="'"+v+"'"  ;    //TX_REMARK
        w="'"+w+"'"  ;    //PROTEC_NO
        x=""+x+""  ;      //PROTEC_PRC
        y=""+y+""  ;      //PROTEC_PKG
        z=""+z+"" ;       //PROTEC_CUFT
        a1="'"+a1+"'";    //PROTEC_REMARK
        a2="'"+a2+"'"  ;   //GN_NO
        a3=""+a3+""  ;     //GN_PRC
        a4=""+a4+""  ;     //GN_PKG
        a5=""+a5+""  ;     //GN_CUFT
        a6="'"+a6+"'"  ;   //GN_REMARK
        a7="'"+a7+"'"  ;   //DEPO_NO
        a8=""+a8+""  ;     //DEPO_PRC
        a9=""+a9+""  ;     //DEPO_PKG
        a10=""+a10+""  ;    //DEPO_CUFT
        a11="'"+a11+"'"  ;  //DEPO_REMARK
        a12="'"+a12+"'"  ;  //KV_NO
        a13=""+a13+""  ;    //KV_PRC
        a14=""+a14+""  ;    //KV_PKG
        a15=""+a15+""  ;    //KV_CUFT
        a16="'"+a16+"'"  ;  //KV_REMARK
        //sample from trn6cat

//        statement ="INSERT INTO TBLCLN ( " +
//                "[PART#], SUPPLIER, DESCRIPTION,OEM_NO,PARTLINK, SUGGEST, CAPA, t24m, t12m, 12MSL, 9msl, 6MSL, 3MSL, RATE, INSTOCK, 1mns, 1M, 1_5M, 2M, 3M, 4M, 5M, ONORDER, SHIPPING, " +
//                "GD, GENERA, DEPO, ee, REMARK, " +
//                "DNK_MEMO, GENERA_MEMO, DEPO_MEMO, NEW, GENERA_P, DEPO_P, GENERA_K, DEPO_K, [GENERA#], [DEPO#], " +
//                "GENERA_C, DEPO_C,GENERA_BO, DEPO_BO ) " +
//
//                "SELECT " +
//                ""+a+", "+c+", "+d+", "+b+", "+f+", 0, IIF('"+e+"'='Y',-1,0), 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, " +
//                "0, 0, 0, 0, '"+a6+"', " +
//                ""+h+", "+l+","+p+", 0, "+j+", "+n+", "+k+", "+o+", "+i+", "+m+", " +
//                "0, 0, 0, 0; ";
        statement=("INSERT INTO TBLMRN ( [PART#], SUPPLIER, DESCRIPTION, T24m, T12m, 12MSL, 9msl, 6MSL, 3MSL, INSTOCK, 1mns, 1M, 1_5M, 2M, 3M, 4M, 5M, ONORDER, SHIPPING, TYG_TW, TYG_TX, GD, GN, " +   //1
                "TYG_TW_P, TYG_TX_P, GD_P, GN_P, " +                                                                                                                                                    //2
                "TYG_TW_K, TYG_TX_K, GD_K, GN_K, " +                                                                                                                                                    //3
                "[TYG_TW#], [TYG_TX#], [GD#], [GN#], REMARK, DNK_MEMO, " +                                                                                                                              //4
                "TYG_TW_MEMO, TYG_TX_MEMO, GD_MEMO, DEPO_MEMO, GN_MEMO, RATE, " +                                                                                                                                  //5
                "TYG_TW_C, TYG_TX_C, GD_C, GN_C, " +                                                                                                                                                    //6
                "TYG_TW_BO, TYG_TX_BO, GD_BO, GN_BO, OEM_NO, PARTLINK, SUGGEST, TYG_TW_STATUS, TYG_TX_STATUS, GD_STATUS, GN_STATUS, NEW, CAPA, MANUAL, DEPO_P,	DEPO_K,	[DEPO#] ) " +                   //7

                "SELECT "+a+", "+c+", "+d+", 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, " +                                                                                            //1
                "IIF(ISNULL("+j+"),0,"+j+"), IIF(ISNULL("+t+"),0,"+t+"), IIF(ISNULL("+o+"),0,"+o+"), IIF(ISNULL("+a3+"),0,"+a3+"), " +                                                                  //2
                "IIF(ISNULL("+k+"),0,"+k+"), IIF(ISNULL("+u+"),0,"+u+"), IIF(ISNULL("+p+"),0,"+p+"), IIF(ISNULL("+a4+"),0,"+a4+"), " +                                                                  //3
                ""+i+", "+s+", "+n+", "+a2+", NULL, "+h+", " +                                                                                                                                         //4
                ""+m+", "+v+", "+r+", "+a11+" , "+a6+", 0, " +                                                                                                                                              //5
                "IIF(ISNULL("+l+"),0,"+l+"), IIF(ISNULL("+l+"),0,"+l+"), IIF(ISNULL("+q+"),0,"+q+"), " +                                                                                //6
                "0, 0, 0, 0, 0, "+b+", "+f+", 0, NULL, NULL, NULL, NULL, 0,IIF('"+e+"'='Y',-1,0),0, IIF(ISNULL("+a8+"),0,"+a8+"),	IIF(ISNULL("+a9+"),0,"+a9+"),	IIF(ISNULL("+a7+"),0,"+a7+")");       //7

        //Execute Statement
        sqlprocess(statement);
    }
    public void CreateNewItemOT(String a, String b, String c, String d, String e, String f,String g,String h,String i,String j,String k,String l,String m,String n,String o,String p,String q,String r,String s
            ,String t,String u,String v,String w,String x,String y, String z, String a1){
        String statement=null;


        a="'"+a+"',"  ;    //ITEM_NO
        b="'"+b+"',"  ;    //PART_NO
        c="'"+c+"',"  ;    //SUPP
        d="'"+d+"',"  ;    //DESCRIPT
        e=""+e+""  ;      //CAPA
        f="'"+f+"',"  ;    //PARTLINK
        g="'"+g+"',"  ;    //POSITION
        h="'"+h+"',"  ;    //DNK_NOTE1
        i="'"+i+"',"  ;    //TYG_NO
        j=""+j+","  ;      //TYG_PRC
        k=""+k+","  ;      //TYG_PKG
        l=""+l+","  ;      //TYG_CUFT
        m="'"+m+"',"  ;    //TYG_REMARK
        n="'"+n+"',"  ;    //GD_NO
        o=""+o+","  ;      //GD_PRC
        p=""+p+","  ;      //GD_PKG
        q=""+q+","  ;      //GD_CUFT
        r="'"+r+"',"  ;    //GD_REMARK
        s="'"+s+"',"  ;    //TX_NO
        t=""+t+","  ;      //TX_PRC
        u=""+u+","  ;      //TX_PKG
        v="'"+v+"',"  ;    //TX_REMARK
        w="'"+w+"',"  ;    //PROTEC_NO
        x=""+x+","  ;      //PROTEC_PRC
        y=""+y+","  ;      //PROTEC_PKG
        z=""+z+"," ;         //PROTEC_CUFT
        a1="'"+a1+"',";       //PROTEC_REMARK




        //sample from trn6cat

        statement = "INSERT INTO TBLOTN ( [PART#], SUPPLIER, DESCRIPTION, T24m, T12m, 12MSL, 9msl, 6MSL, 3MSL, INSTOCK, 1mns, 1M, 1_5M, 2M, 3M, 4M, 5M, ONORDER, SHIPPING, TYG_TW, TYG_TX, GD, PROTEC, " +
                "TYG_TW_P, TYG_TX_P, GD_P, PROTEC_P, TYG_TW_K, TYG_TX_K, GD_K, PROTEC_K, [TYG_TW#], [TYG_TX#], [GD#], [PROTEC#], REMARK, DNK_MEMO, " +
                "TYG_TW_MEMO, TYG_TX_MEMO, GD_MEMO, PROTEC_MEMO, RATE, " +
                "TYG_TW_C, TYG_TX_C, GD_C, PROTEC_C, TYG_TW_BO, TYG_TX_BO, GD_BO, PROTEC_BO, OEM_NO, PARTLINK, SUGGEST, TYG_TW_STATUS, TYG_TX_STATUS, GD_STATUS, PROTEC_STATUS, NEW, CAPA, MANUAL ) " +
                "SELECT "+a+" "+c+" "+d+" 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, " +
                ""+j+" "+t+" "+o+" "+x+""+k+" "+u+" "+p+" "+y+" "+i+" "+s+" "+n+" "+w+" NULL, "+h+" " +
                ""+m+" "+v+" "+r+" "+a1+" 0, " +
                ""+l+" "+l+" "+q+" "+z+" 0, 0, 0, 0, "+b+" "+f+" 0, NULL, NULL, NULL, NULL, 0,IIF('"+e+"'='Y',-1,0), 0 ";
//                "WHERE ITEM_NO NOT IN (SELECT "+a+" FROM TBLBCN);" ;


//
//        statement = "Insert into " +
//                "TBLBCN (ITEM_NO,PART_NO,SUPP,DESCRIPT,CAPA,PARTLINK,POSITION,DNK_NOTE1, " +               //8
//                "TYG_NO,TYG_PRC,TYG_PKG,TYG_CUFT,TYG_REMARK, " +             //TYG_TAIWAN                 //5
//                "GD_NO,GD_PRC,GD_PKG,GD_CUFT, GD_REMARK, " +                 //GOLDEN                     //5
//                "TX_NO,TX_PRC,TX_REMARK, " +                         //TYG_TEXAS                  //4
//                "PROTEC_NO, PROTEC_PRC,PROTEC_PKG, PROTEC_CUFT" +         //PROT (AP)                    //4
//                ") values ("+
//                "'"+a+"',"    +                       //ITEM_NO
//                "'"+b+"',"    +                       //PART_NO
//                "'"+c+"',"    +                       //SUPP
//                "'"+d+"',"    +                       //DESCRIPT
//                ""+e+","    +                         //CAPA
//                "'"+f+"',"    +                       //PARTLINK
//                "'"+g+"',"    +                       //POSITION
//                "'"+h+"',"    +                       //DNK_NOTE1
//                "'"+i+"',"    +                       //TYG_NO
//                ""+j+","    +                         //TYG_PRC
//                ""+k+","    +                         //TYG_PKG
//                ""+l+","    +                         //TYG_CUFT
//                "'"+m+"',"    +                       //TYG_REMARK
//                "'"+n+"',"    +                       //GD_NO
//                ""+o+","    +                         //GD_PRC
//                ""+p+","    +                         //GD_PKG
//                ""+q+","    +                         //GD_CUFT
//                "'"+r+"',"    +                       //GD_REMARK
//                "'"+s+"',"    +                       //TX_NO
//                ""+t+","    +                         //TX_PRC
////                ""+u+","    +                         //TX_PKG
//                "'"+u+"',"    +                       //TX_REMARK
//                "'"+v+"',"    +                       //PROTEC_NO
//                ""+w+","    +                         //PROTEC_PRC
//                ""+x+","    +                         //PROTEC_PKG
//                ""+y+")";                             //PROTEC_CUFT
//                ""+a1+")"    +                        //PROTEC_CUFT
//                "'"+a1+"')"    +                       //GD_REMARK
//                "'"+a2+"')"    +                       //GD_REMARK




        //Execute Statement
        sqlprocess(statement);
    }

    public void updTblnWithInv (String tbl, String itm){
        DnkFunctions dnkFunctions = new DnkFunctions();
        String table;
        table=dnkFunctions.getCategoryNAME(tbl);
        sqlprocess("UPDATE TBL"+table+"N AS A INNER JOIN TBLINVENTORY AS B ON A.[PART#]=B.[PART#] SET A.DESCRIPTION = B.DESCRIPTION WHERE A.DESCRIPTION <> B.DESCRIPTION OR ISNULL(A.DESCRIPTION);");
//        sqlprocess("UPDATE TBLCLN AS A INNER JOIN TBLINVENTORY AS B ON A.[PART#]=B.[PART#] SET A.DESCRIPTION = B.DESCRIPTION WHERE A.DESCRIPTION <> B.DESCRIPTION OR ISNULL(A.DESCRIPTION);");
//        sqlprocess("UPDATE TBLHFN AS A INNER JOIN TBLINVENTORY AS B ON A.[PART#]=B.[PART#] SET A.DESCRIPTION = B.DESCRIPTION WHERE A.DESCRIPTION <> B.DESCRIPTION OR ISNULL(A.DESCRIPTION);");
//        sqlprocess("UPDATE TBLLTN AS A INNER JOIN TBLINVENTORY AS B ON A.[PART#]=B.[PART#] SET A.DESCRIPTION = B.DESCRIPTION WHERE A.DESCRIPTION <> B.DESCRIPTION OR ISNULL(A.DESCRIPTION);");
//        sqlprocess("UPDATE TBLMRN AS A INNER JOIN TBLINVENTORY AS B ON A.[PART#]=B.[PART#] SET A.DESCRIPTION = B.DESCRIPTION WHERE A.DESCRIPTION <> B.DESCRIPTION OR ISNULL(A.DESCRIPTION);");
//        sqlprocess("UPDATE TBLOTN AS A INNER JOIN TBLINVENTORY AS B ON A.[PART#]=B.[PART#] SET A.DESCRIPTION = B.DESCRIPTION WHERE A.DESCRIPTION <> B.DESCRIPTION OR ISNULL(A.DESCRIPTION);");



        sqlprocess("UPDATE TBL"+table+"N AS A INNER JOIN TBLINVENTORY AS B ON A.[PART#]=B.[PART#] SET A.POSITION = B.POSITION WHERE A.POSITION <> B.POSITION OR ISNULL(A.POSITION);");
//        sqlprocess("UPDATE TBLCLN AS A INNER JOIN TBLINVENTORY AS B ON A.[PART#]=B.[PART#] SET A.POSITION = B.POSITION WHERE A.POSITION <> B.POSITION OR ISNULL(A.POSITION);");
//        sqlprocess("UPDATE TBLHFN AS A INNER JOIN TBLINVENTORY AS B ON A.[PART#]=B.[PART#] SET A.POSITION = B.POSITION WHERE A.POSITION <> B.POSITION OR ISNULL(A.POSITION);");
//        sqlprocess("UPDATE TBLLTN AS A INNER JOIN TBLINVENTORY AS B ON A.[PART#]=B.[PART#] SET A.POSITION = B.POSITION WHERE A.POSITION <> B.POSITION OR ISNULL(A.POSITION);");
//        sqlprocess("UPDATE TBLMRN AS A INNER JOIN TBLINVENTORY AS B ON A.[PART#]=B.[PART#] SET A.POSITION = B.POSITION WHERE A.POSITION <> B.POSITION OR ISNULL(A.POSITION);");
//        sqlprocess("UPDATE TBLOTN AS A INNER JOIN TBLINVENTORY AS B ON A.[PART#]=B.[PART#] SET A.POSITION = B.POSITION WHERE A.POSITION <> B.POSITION OR ISNULL(A.POSITION);");
    }
    public boolean checkDupMod(String st) {               //Establish SQL connection to server and prep for statement execute to return if itme is DUPLICATED.
        boolean foundDup = false;
        try {
            int rowsupdated;

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dataSourceName = site;
            String dbURL = (new StringBuilder()).append("jdbc:odbc:").append(dataSourceName).toString();
            Connection conli = DriverManager.getConnection(dbURL, "", "");


            Statement stli = conli.createStatement();

            try {

//                System.out.println("SQL :" + st);
//                rowsupdated = stli.executeUpdate(st);
//                System.out.println("Total record Updated : " + rowsupdated);
//                //JOptionPane.showOptionDialog(null, "Statement : " + st[x] + "\n\nTotal record Updated : " + rowsupdated, "Update Complete", JOptionPane.DEFAULT_OPTION, JOptionPane.INFORMATION_MESSAGE, null, new Object[]{}, null);
//                if (stli.getMaxRows()>0) {
//                    foundDup = true;
//                } else if (stli.getMaxRows()<0){
//                    foundDup =false;
//                }
                stli.execute(st);
                ResultSet rs = stli.getResultSet();
//                System.out.println("first :"+rs.getRow());
                rs.next();
//                System.out.println("second :"+rs.getRow());
                if(rs.getRow()>0)
                {
//                    System.out.println("Found something");
                    foundDup=true;
//                    String DESCRIPT;
//                    for(; rs.next(); st1.execute((new StringBuilder()).append("INSERT INTO TBLNU (ITEM_NO,PART_NO,BRAND,MODEL,CATALOG,DESCRIPT,R_COST,COST,RETAIL,QOH,CONTROL,LI_WARE,POSITION,NEW_DES,NEW_MOD,YR_L,YR_H,REMARK) VALUES('").append(rs.getString("ITEM_NO")).append("','").append(rs.getString("PART_NO")).append("','").append(rs.getString("BRAND")).append("','").append(rs.getString("MODEL")).append("',").append(dnk.getCategoryID(rs.getString("CATALOG"))).append(",'").append(DESCRIPT).append("',").append(rs.getDouble("R_COST")).append(",").append(rs.getDouble("COST")).append(",").append(rs.getDouble("RETAIL")).append(",").append(rs.getDouble("QOH")).append(",'").append(rs.getString("CONTROL")).append("',").append(rs.getDouble("LI_WARE")).append(",'").append(rs.getString("POSITION")).append("','").append(rs.getString("NEW_DES")).append("','").append(rs.getString("NEW_MOD")).append("',").append(rs.getDouble("YR_L")).append(",").append(rs.getDouble("YR_H")).append(",'").append(rs.getString("REMARK")).append("');").toString()))
//                    {
//                        DESCRIPT = rs.getString("DESCRIPT");
//                        DESCRIPT = DESCRIPT.replaceAll("'", " ");
//                    }

                } else if (rs.getRow()<=0){
//                    System.out.println("Found nothing");
                    foundDup=false;
                }



            } catch (SQLException er) {
//                er.printStackTrace();
//                System.out.println(er);
//                foundDup=false;
            }
            conli.close();
            stli.close();

        } catch (ClassNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
//        System.out.println("Found Dupmod Result SQLEXEC :"+foundDup);
        return foundDup;

    }
    public boolean checkDupItem(String st){               //Establish SQL connection to server and prep for statement execute to return if itme is DUPLICATED.
        boolean foundDup = false;
        try {
            int rowsupdated;

            Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
            String dataSourceName = site;
//            System.out.println("show site : "+site);
            String dbURL = (new StringBuilder()).append("jdbc:odbc:").append(dataSourceName).toString();
            Connection conli = DriverManager.getConnection(dbURL, "", "");


            Statement stli = conli.createStatement();

            try {

//                System.out.println("SQL :" + st);
//                rowsupdated = stli.executeUpdate(st);
//                System.out.println("Total record Updated : " + rowsupdated);
//                //JOptionPane.showOptionDialog(null, "Statement : " + st[x] + "\n\nTotal record Updated : " + rowsupdated, "Update Complete", JOptionPane.DEFAULT_OPTION, JOptionPane.INFORMATION_MESSAGE, null, new Object[]{}, null);
//                if (stli.getMaxRows()>0) {
//                    foundDup = true;
//                } else if (stli.getMaxRows()<0){
//                    foundDup =false;
//                }
                stli.execute(st);
                ResultSet rs = stli.getResultSet();
//                System.out.println("first :"+rs.getRow());
                rs.next();
//                System.out.println("second :"+rs.getRow());
                if(rs.getRow()>0)
                {
//                    System.out.println("Found something");
                    foundDup=true;
//                    String DESCRIPT;
//                    for(; rs.next(); st1.execute((new StringBuilder()).append("INSERT INTO TBLNU (ITEM_NO,PART_NO,BRAND,MODEL,CATALOG,DESCRIPT,R_COST,COST,RETAIL,QOH,CONTROL,LI_WARE,POSITION,NEW_DES,NEW_MOD,YR_L,YR_H,REMARK) VALUES('").append(rs.getString("ITEM_NO")).append("','").append(rs.getString("PART_NO")).append("','").append(rs.getString("BRAND")).append("','").append(rs.getString("MODEL")).append("',").append(dnk.getCategoryID(rs.getString("CATALOG"))).append(",'").append(DESCRIPT).append("',").append(rs.getDouble("R_COST")).append(",").append(rs.getDouble("COST")).append(",").append(rs.getDouble("RETAIL")).append(",").append(rs.getDouble("QOH")).append(",'").append(rs.getString("CONTROL")).append("',").append(rs.getDouble("LI_WARE")).append(",'").append(rs.getString("POSITION")).append("','").append(rs.getString("NEW_DES")).append("','").append(rs.getString("NEW_MOD")).append("',").append(rs.getDouble("YR_L")).append(",").append(rs.getDouble("YR_H")).append(",'").append(rs.getString("REMARK")).append("');").toString()))
//                    {
//                        DESCRIPT = rs.getString("DESCRIPT");
//                        DESCRIPT = DESCRIPT.replaceAll("'", " ");
//                    }

                } else if (rs.getRow()<=0){
//                    System.out.println("Found nothing");
                    foundDup=false;
                }




            } catch (SQLException er) {
//                er.printStackTrace();
//                System.out.println(er);
//                foundDup=false;
            }

            stli.close();
            conli.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } catch (SQLException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
//        System.out.println("Found Dupmod Result SQLEXEC :"+foundDup);
        return foundDup;
    }
    public void modelKey(String mod_One, String m_key){
        sqlprocess("INSERT INTO MOD_KEY ( [MOD_ONE], [M_KEY] ) " +
                "SELECT '"+mod_One+"','"+m_key+"';");
    }
    public void sqlprocess(String st){               //Establish SQL connection to server and prep for statement execute.
        try{
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String dataSourceName = site;
                String dbURL = (new StringBuilder()).append("jdbc:odbc:").append(dataSourceName).toString();
                Connection conli = DriverManager.getConnection(dbURL, "", "");

            Statement stli = conli.createStatement();

            try {
//                System.out.println("Running SQL at site :"+site);
//                System.out.println("Script :"+st);
                int rowsupdated = stli.executeUpdate(st);
//                System.out.println("Total record Updated : "+rowsupdated);
                //JOptionPane.showOptionDialog(null, "Statement : " + st[x] + "\n\nTotal record Updated : " + rowsupdated, "Update Complete", JOptionPane.DEFAULT_OPTION, JOptionPane.INFORMATION_MESSAGE, null, new Object[]{}, null);
                conli.close();
            }catch(SQLException er) {
                er.printStackTrace();
            }
            conli.close();
            stli.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        } catch (SQLException e) {

            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }


    }
    public void AddNewPart(){
        {
            try
            {
                DnkFunctions dnk = new DnkFunctions();
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
                String dataSourceName = site;
                String dbURL = (new StringBuilder()).append("jdbc:odbc:").append(dataSourceName).toString();
                Connection con = DriverManager.getConnection(dbURL, "", "");
                Statement st = con.createStatement();
                Statement st1 = con.createStatement();
                Statement st2 = con.createStatement();
                Statement st3 = con.createStatement();
                Statement st4 = con.createStatement();
                Statement st5 = con.createStatement();
                Statement st6 = con.createStatement();

                try
                {
                    st3.execute("select * from tblnu;");
                    ResultSet rs1 = st3.getResultSet();
                    if(rs1 != null)
                        while(rs1.next())
                        {
//                        String CONTROL = rs1.getString("CONTROL").replaceAll("null", " ");
                            String CONTROL = " ";
                            String mmo = CONTROL + rs1.getString("REMARK");
                            String mmo1 = mmo.trim();
                            try
                            {
                                String statement="INSERT INTO tblinventory ( categoryid, [part#], description, unittype, unit, unitcost, sellingprice, instock, onorder, onhold, onrma, parvalue, location, serialized, [vendor#], discontinued, weight, taxable, vendor1id, vendor2id, vendor3id, lpp, lpf, ldp, lqp, [upc#], revenueglid, inventoryglid, currentcost, warehouseid , [product#], internet, internetprice, cogid, addinfulfillmentallowed, make, mdl, modeltype, beginyr, endyr, prefix, nagscode, dnkcode, cif, retailprice, sellingprice2, [memo], westwp, fob_t, ecp, ppgprice, slprice, mprice, shapeid, country, [bin#], prpkey, ppgloc, instock1, POSITION, NEW_DES ) SELECT top 1 b.CATALOG, b.item_no, b.descript, NULL, NULL, b.r_cost, b.retail, b.qoh, 0, 0, 0, 0, b.location, no, NULL, no, 0, yes, 0, 0, 0, 0, 0, 0, 0, NULL, 1, 1, 0, "+wareHouse+", b.part_no, yes, b.retail, 1, yes, b.brand, B.MODEL, B.NEW_MOD, b.yr_l, b.yr_h, NULL, NULL, b.item_no, b.cost, b.retail, b.retail, '" + mmo1 + "', 0, 0, " + "0, 0, 0, 0, 18, NULL, NULL, 0, NULL, " + "B.LI_WARE, POSITION, NEW_DES " + "FROM TBLNU AS b where b.item_no ='" + rs1.getString("ITEM_NO") + "';";
                                sqlprocess(statement);
//                                st2.execute("INSERT INTO tblinventory ( categoryid, [part#], description, unittype, unit, unitcost, sellingprice, instock, onorder, onhold, onrma, parvalue, location, serialized, [vendor#], discontinued, weight, taxable, vendor1id, vendor2id, vendor3id, lpp, lpf, ldp, lqp, [upc#], revenueglid, inventoryglid, currentcost, + wareHouse + , [product#], internet, internetprice, cogid, addinfulfillmentallowed, make, mdl, modeltype, beginyr, endyr, prefix, nagscode, dnkcode, cif, retailprice, sellingprice2, [memo], westwp, fob_t, ecp, ppgprice, slprice, mprice, shapeid, country, [bin#], prpkey, ppgloc, instock1, POSITION, NEW_DES ) SELECT top 1 b.CATALOG, b.item_no, b.descript, NULL, NULL, b.r_cost, b.retail, b.qoh, 0, 0, 0, 0, b.location, no, NULL, no, 0, yes, 0, 0, 0, 0, 0, 0, 0, NULL, 1, 1, 0, 2, b.part_no, yes, b.retail, 1, yes, b.brand, B.MODEL, B.NEW_MOD, b.yr_l, b.yr_h, NULL, NULL, b.item_no, b.cost, b.retail, b.retail, '" + mmo1 + "', 0, 0, " + "0, 0, 0, 0, 18, NULL, NULL, 0, NULL, " + "B.LI_WARE, POSITION, NEW_DES " + "FROM TBLNU AS b where b.item_no ='" + rs1.getString("ITEM_NO") + "';");
                            }
                            catch(SQLException er) { }
                        }
//                    System.out.println("tblinventory");
                }
                catch(SQLException er)
                {
                    er.printStackTrace();
                }
                String statement1="INSERT INTO tblmodel ( [part#], make, mdl, modeltype, Description, Beginyr, Endyr, warehouseid, position, new_des ) SELECT item_no, brand, model, new_mod, descript, yr_l, yr_h, "+wareHouse+", position, new_des FROM tblnu;";
                sqlprocess(statement1);
//                    st5.execute("INSERT INTO tblmodel ( [part#], make, mdl, modeltype, Description, Beginyr, Endyr, +wareHouse+, position, new_des ) SELECT item_no, brand, model, new_mod, descript, yr_l, yr_h, 2, position, new_des FROM tblnu;");

//                System.out.println("tblmodel");
                try
                {
                    st6.execute("INSERT INTO TBLINVENTORYCOSTING ( INVID, QTY, DATERECEIVED, USED, COST, VENDORID ) SELECT DISTINCT A.INVID, A.INSTOCK, NOW(), 0, 0, NULL FROM TBLINVENTORY AS A INNER JOIN tblnu AS B ON A.[PART#]=B.[ITEM_NO];");
                }
                catch(SQLException er) { }
//                System.out.println("tblinventorycosting");
                st.close();
                st1.close();
                st2.close();
                st3.close();
                st4.close();
                st5.close();
                st6.close();
                con.close();
            }
            catch(Exception e) {
                e.printStackTrace();
            }
//            System.out.println("Add item to "+site+" Complete");
        }
    }


}
